<?php
/*
 * Created on 19 Aug 2007
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
require_once(dirname(dirname(__FILE__))."/common.php");

function getSkillsForm(){
	global $page_owner;
	$skillcats = get_records_select("skillcategory","","","scname ASC");
	$html = '<h2>Add Skill</h2>';
	
	//skill category
	$html .= '<div class="infoholder"><div class="fieldname"><h3>Category</h3></div><p>';
	$html .= '<select id="skillcatid" name="skillcatid" onchange="loadSkills();">';
 	if ($skillcats){
 		$html .= "<option value='0' selected='true'></option>";
	 	foreach ($skillcats as $cat){
	 		$html .= "<option value='".$cat->ident."'>".$cat->scname."</option>";
	 	}
 	}
 	$html .= '</select></p>';
 	$html .= '</div>';
 	
 	//skill
 	$html .= '<div class="infoholder"><div class="fieldname"><h3>Skill</h3></div><p>';
	$html .= '<select id="skillid" name="skillid" >';
 	$html .= '</select></p>';
 	$html .= '</div>';
 	
 	//skill
 	$html .= '<div class="infoholder"><p>';
	$html .= '<input type="checkbox" id="skillwant" name="skillwant" value="Y"/>This is a skill I would like to develop';
 	$html .= '</p>';
 	$html .= '</div>';
 	
 	//date
 	$html .= '<div class="infoholder">';
	$html .= '<div class="fieldname"><h3>Date</h3></div><p>';
	$html .= '<input type="text" name="skilldate_day" id="skilldate_day" size="1" maxlength="2" value="dd" onfocus="blankfield(\'skilldate_day\',\'dd\')"/> - ';
	$html .= '<input type="text" name="skilldate_month" id="skilldate_month" size="1" maxlength="2" value="mm" onfocus="blankfield(\'skilldate_month\',\'mm\')"/> - ';
	$html .= '<input type="text" name="skilldate_year" id="skilldate_year" size="2" maxlength="4" value="yyyy" onfocus="blankfield(\'skilldate_year\',\'yyyy\')"/>';
 	$html .= '</p></div>';
 	
 	//location
 	$html .= '<div class="infoholder"><div class="fieldname"><h3>Location</h3></div><p>';
	$html .= '<input type="text" value="" name="skilllocation" id="skilllocation" style="width:95%"/>';
 	$html .= '</p></div>';
 	
 	//notes
 	$html .= '<div class="infoholder"><div class="fieldname"><h3>Notes</h3></div><p>';
	$html .= '<textarea id="skillnotes" style="width: 95%; height: 100px;" name="skillnotes"></textarea>';
 	$html .= '</p></div>';
 	
 	
 	//evidence
 	$files = get_records_select("files","owner=".$page_owner);
 	//
 	$html .= '<div class="infoholder"><div class="fieldname"><h3>Evidence</h3></div>';
	$html .= '<ul id="evidencelist"></ul>';
	$html .= '<p>';
	$html .= '<select id="fileid" name="fileid">';
	if($files){
		foreach($files as $file){
			$html .= '<option value="'.$file->ident.'">';
			if ($file->title){
				$html .= $file->title; 
			} else {
				$html .= $file->originalname;
			}
			$html .= '</option>';
		}
	}
	$html .= '</select>';
 	$html .= '<input type="button" value="Add" onclick="addEvidence();"/></p></div>';
 	
 	
 	$html .= '<p><input type="button" value="Add Skill" onclick="addSkill()"/></p>';
 	
 	return $html;
}


function displayUserSkills($options = true,$showifblank = true, $header = ""){
	global $CFG;
	global $page_owner;
	global $profile_name;
	global $USER;
	
 	$sql = 'SELECT us.ident, s.skillname, us.us_date, us.us_location, us.us_notes, us.us_want FROM '.$CFG->prefix.'skill s '.
			'INNER JOIN '.$CFG->prefix.'users_skill us ON us.skill_id = s.ident '.
			'WHERE us.user_id = '.$page_owner
			.' ORDER BY us.us_date DESC';
	$skills = get_records_sql($sql);
	$html = "";
	
	if($showifblank || $skills){
		$html .= $header;
	 	$html .= '<table id="userskills" class="vptable" width="100%"><tr id="skillrowheader">';
	 	$html .= '<th width="35%">Skill</th>';
	 	$html .= '<th width="20%">Date & Location</th>';
	 	$html .= '<th width="20%">Notes</th>';
	 	$html .= '<th width="20%">Evidence</th>';
	 	if ($options && $page_owner == $USER->ident){
	 		$html .= '<th>Options</th>';
	 	}
	 	$html .= '</tr>';
	 	if ($skills){
		 	foreach ($skills as $skill){
		 		$html .= "<tr id='skillrow".$skill->ident."' class='vp'>";
		 		$html .= "<td class='vp'>";
		 		$html .= "<div name='popupedit' class='popupedithidden' id='popupedit".$skill->ident."'></div>";
		 		$html .= $skill->skillname."</td>";
		 		if($skill->us_want == "N"){
		 			$html .= "<td class='vp'>".formatDate($skill->us_date).",".$skill->us_location."</td>";
		 		} else {
		 			$html .= "<td class='vp'>To develop</td>";
		 		}
		 		$html .= "<td class='vp'>".$skill->us_notes."</td>";
		 		$html .= "<td class='vp'>";
		 			// get the evidence
		 			$sql = 'SELECT f.ident, f.title, f.folder, f.originalname FROM '.$CFG->prefix.'userskill_evidence usevid '.
							'INNER JOIN '.$CFG->prefix.'files f ON usevid.file_id = f.ident '.
							'WHERE usevid.us_id = '.$skill->ident;
					$evids = get_records_sql($sql);
					if ($evids){
						$html .="<ul class='evidencelist'>";
						foreach ($evids as $evid){
							if($evid->title != ''){
								$filename = $evid->title;
							}else {
								$filename = $evid->originalname;
							}
							$html .= "<li><a target='_blank' href='".$CFG->wwwroot.$profile_name."/files/".$evid->folder."/".$evid->ident."/".$evid->originalname."'>".$filename."</a></li>";
						}
						$html .="</ul>";
					}
				$html .= "</td>";
				if ($options && $page_owner == $USER->ident){
		 			$html .= "<td class='vp'><span onclick='removeSkill(".$skill->ident.",\"".$skill->skillname."\")' class='remove'>[remove]</span>";
		 			$html .= "<span onclick='editSkill(".$skill->ident.")' class='remove'>[edit]</span></td>";
				}
		 		$html .= "</tr>";
		 	}
	 	}
	 	$html .= '</table>';
	}
 	return $html;
}

function displayAdminSkillCat(){
	
	global $CFG;
	$sql = 'SELECT sc.ident, sc.scname, COUNT(scs.sc_id) as inuse FROM '.$CFG->prefix.'skillcategory sc '.
				'LEFT OUTER JOIN '.$CFG->prefix.'skillcat_skill scs ON sc.ident = scs.sc_id '.
				'GROUP BY sc.ident, sc.scname ORDER BY sc.scname ASC';
	$skillcats = get_records_sql($sql);
 	$html = '<table id="skillcategories" name="skillcategories" width="100%"><tr>';
 	$html .= '<th>Skill Category</th>';
 	$html .= '<th width="20%">Options</th>';
 	$html .= '</tr>';
 	if ($skillcats){
	 	foreach ($skillcats as $skillcat){
	 		$html .= "<tr id='skillcatrow".$skillcat->ident."'>";
	 		$html .= "<td id='skillcatrow".$skillcat->ident."edit'>".$skillcat->scname."</td>";
	 		$html .= "<td id='skillcatrow".$skillcat->ident."options'>";
	 		if ($skillcat->inuse == 0){ 	
	 			$html .= "<span onclick='editAdminSkillCat(".$skillcat->ident.")' class='remove'>[edit]</span>";		
				$html .= "<span onclick='deleteAdminSkillCat(".$skillcat->ident.",\"".$skillcat->scname."\")' class='remove'>[delete]</span>";	
	 		}
	 		$html .= "</td>";
	 		$html .= "</tr>";
	 	}
 	}
 	
 	//now add row for adding a new course
 	$html .= '<tr id="addskillcatrow"><td><input type="text" name="skillcatname" size="40" id="skillcatname">';
 	$html .= '<input type="button" onclick="addAdminSkillCat()" value="Add"></td><td></td></tr>';
 	
 	$html .= '</table>';
	return $html;

}

function getAdminSkillCat($scId){
	$skillcat = get_record_select("skillcategory","ident=".$scId);
	return $skillcat;
}

function editAdminSkillCat($scId,$scName){
	global $CFG;	
	//first check curse isn't in use
	$sql = 'SELECT scs.sc_id, COUNT(*) as inuse FROM '.$CFG->prefix.'skillcat_skill scs '.
			'WHERE scs.sc_id = '. $scId. ' GROUP BY scs.sc_id';
	$result = get_record_sql($sql);
	
	$edit = new StdClass;
	$edit->ident = $scId;
	if ($result->inuse == 0){
		$edit->scname = $scName;
		$edit->last_updated = mktime();
		update_record('skillcategory',$edit);
		$edit->edited = 'true';	
	} else {
		$edit->edited = 'false';
		$edit->error = 'Skill category can\'t be edited because it\'s already in use';
	}
	return $edit;
}

function deleteAdminSkillCat($scId){
	global $CFG;	
	//first check category isn't in use
	$sql = 'SELECT scs.sc_id, COUNT(*) as inuse FROM '.$CFG->prefix.'skillcat_skill scs '.
			'WHERE scs.sc_id = '. $scId. ' GROUP BY scs.sc_id';
	$result = get_record_sql($sql);
	
	$delete = new StdClass;
	$delete->ident = $scId;
	if ($result->inuse == 0){
		delete_records_select('skillcategory','ident = '. $scId);
		$delete->deleted = 'true';	
	} else {
		$delete->deleted = 'false';
		$edit->error = 'Skill category can\'t be edited because it\'s already in use';
	}
	return $delete;
}

function addAdminSkillCat($scName){
	global $CFG;
	$skillcat = new StdClass;
	$skillcat->scname = $scName;
	$skillcat->last_updated = mktime();
	$newId = insert_record('skillcategory',$skillcat);
	
	// now return the course
	$sql = 'SELECT sc.* FROM '.$CFG->prefix.'skillcategory sc '.
			'WHERE sc.ident = '.$newId;
	$skillcat = get_record_sql($sql); 
	return $skillcat;
}

function displaySkillsFramework(){
	global $CFG;
	$sql = 'SELECT scs.ident,sc.scname, s.skillname, s.skillurl FROM '.$CFG->prefix.'skillcategory sc
			INNER JOIN '.$CFG->prefix.'skillcat_skill scs ON sc.ident = scs.sc_id
			INNER JOIN '.$CFG->prefix.'skill s ON scs.skill_id = s.ident
			ORDER BY scname ASC,skillname ASC';
	$records = get_records_sql($sql);
	$html = '';
	$curcat = '';
	$count = 0;
	if ($records){
	 	foreach ($records as $record){
	 		if($record->scname != $curcat){
	 			if($count != 0){
	 				$html .= '</ul>';
	 			}
	 			$html .= '<h3>'.$record->scname.'</h3><ul>';
	 			$curcat = $record->scname;
	 		}
	 		$html .= '<li>'.$record->skillname;
	 		if($record->skillurl !=""){
	 			$html .= ' <a href="'.$record->skillurl.'" target="new">[more info]</a>';
	 		}
			$html .= '</li>';
	 		$count++;
	 	}
	 	$html .= '</ul>';
	}
	return $html;
}


function displayAdminSkill(){
	
	global $CFG;
	$sql = 'SELECT s.ident, s.skillname, s.skillurl, COUNT(us.user_id) as inuse FROM '.$CFG->prefix.'skill s '.
				'LEFT OUTER JOIN '.$CFG->prefix.'users_skill us ON s.ident = us.skill_id '.
				'GROUP BY s.ident, s.skillname ORDER BY s.skillname ASC';
	$skills = get_records_sql($sql);
	
	$cats = get_records_select("skillcategory");
	
	
 	$html = '<table id="skill" name="skill" width="100%"><tr>';
 	$html .= '<th>Skill</th>';
 	$html .= '<th width="30%">Categories</th>';
 	$html .= '<th width="20%">Options</th>';
 	$html .= '</tr>';
 	if ($skills){
	 	foreach ($skills as $skill){
	 		$html .= "<tr id='skillrow".$skill->ident."'>";
	 		$html .= "<td id='skillrow".$skill->ident."edit'>".$skill->skillname."<br/><a href='".$skill->skillurl."' target='new'>".$skill->skillurl."</a></td>";
	 		
	 		$html .= "<td id='skillrow".$skill->ident."category'>";
			// get the categories for this skill
			$sql = 'SELECT sc.ident, sc.scname FROM '.$CFG->prefix.'skillcategory sc '.
					'INNER JOIN '.$CFG->prefix.'skillcat_skill scs ON scs.sc_id = sc.ident '.
					'WHERE scs.skill_id = '.$skill->ident; 
			$skillcats = get_records_sql($sql);
			$html .="<ul id='skillcatlist".$skill->ident."' class='skillcategorylist'>";	
			if ($skillcats){
				foreach ($skillcats as $skillcat){
					$html .= "<li id='skillskillcat_".$skill->ident."_".$skillcat->ident."'>";
					$html .= $skillcat->scname." <span onclick='removeAdminSkillSkillCat(".$skill->ident.",".$skillcat->ident.")' class='remove'>[remove]</span>";
					$html .= "</li>";
				}
			}
			$html .= "</ul>";
			$html .= "<span onclick='getCategories(".$skill->ident.")' class='remove' id='addCatSpan".$skill->ident."'>[Add Category]</span>";
			$html .= "</td>";
	 		
	 		
	 		$html .= "<td id='skillrow".$skill->ident."options'>";
	 		if ($skill->inuse == 0){ 	
	 			$html .= "<span onclick='editAdminSkill(".$skill->ident.")' class='remove'>[edit]</span>";		
				$html .= "<span onclick='deleteAdminSkill(".$skill->ident.",\"".$skill->skillname."\")' class='remove'>[delete]</span>";	
	 		}
	 		$html .= "</td>";
	 		$html .= "</tr>";
	 	}
 	}
 	
 	//now add row for adding a new course
 	$html .= '<tr id="addskillrow"><td><input type="text" name="skillname" size="40" id="skillname">';
 	$html .= '<input type="button" onclick="addAdminSkill()" value="Add"></td><td></td><td></td></tr>';
 	
 	$html .= '</table>';
	return $html;

}

function removeAdminSkillSkillCat($skillId,$scId){
	global $CFG;	
	
	$delete = new StdClass;
	$delete->scid = $scId;
	$delete->skillid = $skillId;
	if ($result->inuse == 0){
		delete_records_select('skillcat_skill','sc_id='. $scId .' AND skill_id='.$skillId);
		$delete->deleted = 'true';	
	} else {
		$delete->deleted = 'false';
		$edit->error = 'Error removing category';
	}
	return $delete;
}

function addAdminSkillSkillCat($skillId,$scId){
	global $CFG;
	$new = new StdClass;
	$new->sc_id = $scId;
	$new->skill_id = $skillId;
	$newId = insert_record('skillcat_skill',$new);
	
	// now return the course
	$sql = 'SELECT sc.*, scs.* FROM '.$CFG->prefix.'skillcategory sc '.
			'INNER JOIN '.$CFG->prefix.'skillcat_skill scs ON scs.sc_id = sc.ident '.
			'WHERE scs.ident = '.$newId;
	$rec = get_record_sql($sql); 
	return $rec;
}

function getAdminSkill($skillId){
	$skill = get_record_select("skill","ident=".$skillId);
	return $skill;
}

function editAdminSkill($skillId,$skillName,$skillURL){
	global $CFG;	
	//first check curse isn't in use
	$sql = 'SELECT us.ident, COUNT(*) as inuse FROM '.$CFG->prefix.'users_skill us '.
				'WHERE us.skill_id='.$skillId.' GROUP BY us.idente';
	$result = get_record_sql($sql);
	$edit = new StdClass;
	$edit->ident = $skillId;
	if ($result->inuse == 0){
		$edit->skillname = $skillName;
		$edit->skillurl = $skillURL;
		$edit->last_updated = mktime();
		update_record('skill',$edit);
		$edit->edited = 'true';	
	} else {
		$edit->edited = 'false';
		$edit->error = 'Skill can\'t be edited because it\'s already in use';
	}
	return $edit;
}

function deleteAdminSkill($skillId){
	global $CFG;	
	//first check category isn't in use
	$sql = 'SELECT us.user_id, COUNT(*) as inuse FROM '.$CFG->prefix.'users_skill us '.
			'WHERE us.skill_id = '. $skillId. ' GROUP BY us.user_id';
	$result = get_record_sql($sql);
	
	$delete = new StdClass;
	$delete->ident = $skillId;
	if ($result->inuse == 0){
		//delete the records in prefix_skillcat_skill
		delete_records_select('skillcat_skill','skill_id = '. $skillId);
		delete_records_select('skill','ident = '. $skillId);
		$delete->deleted = 'true';	
	} else {
		$delete->deleted = 'false';
		$edit->error = 'Skill category can\'t be edited because it\'s already in use';
	}
	return $delete;
}

function addAdminSkill($skillName){
	global $CFG;
	$skill = new StdClass;
	$skill->skillname = $skillName;
	$skill->last_updated = mktime();
	$newId = insert_record('skill',$skill);
	
	// now return the course
	$sql = 'SELECT s.* FROM '.$CFG->prefix.'skill s '.
			'WHERE s.ident = '.$newId;
	$skill = get_record_sql($sql); 
	return $skill;
}

function getAdminCategories($skillId){
	$cats = get_records_select("skillcategory","","","scname ASC");
	$skill = new StdClass;
	$skill->skillid = $skillId;
	$skill->categories = $cats;
	return $skill;
}

function getSkillsForCategory($scId, $usId){
	global $CFG;
	$sql = 'SELECT s.* FROM '.$CFG->prefix.'skill s '.
			'INNER JOIN '.$CFG->prefix.'skillcat_skill scs ON scs.skill_id = s.ident '.
			'WHERE scs.sc_id = '.$scId .
			' ORDER BY skillname ASC';
	
	$skills = get_records_sql($sql);
	$skill = new StdClass;
	$skill->scid = $scId;
	$skill->usid = $usId;
	$skill->skills = $skills;
	return $skill;
}

function getFile($fileId,$usId){
	global $USER;
	$file = get_record_select("files","ident=".$fileId." AND owner=".$USER->ident);
	$file->usid = $usId;
	return $file;
}

function addSkill($skillId,$skillDate,$skillLoc,$skillNotes,$skillEvidence,$skillWant){
	global $CFG;
	global $page_owner;
	$skill = new StdClass;
	$skill->user_id = $page_owner;
	$skill->skill_id = $skillId;
	$skill->us_date = $skillDate;
	$skill->us_location = $skillLoc;
	$skill->us_notes = $skillNotes;
	$skill->us_want = $skillWant;
	$newId = insert_record('users_skill',$skill, true);
	// add evidence
	
	$evidenceFiles = split(',',$skillEvidence);

	foreach ($evidenceFiles as $key => $val){
		if($val){
			$e = new StdClass;
			$e->us_id = $newId;
			$e->file_id = $val;
			insert_record('userskill_evidence',$e);
		}
	}
	
	return getUserSkill($newId);
}

function getUserSkill($ident){
	global $CFG;
	$sql = 'SELECT us.ident, us.skill_id, s.skillname, us.us_date, us.us_location, us.us_notes, us.us_want FROM '.$CFG->prefix.'skill s '.
			'INNER JOIN '.$CFG->prefix.'users_skill us ON us.skill_id = s.ident '.
			'WHERE us.ident = '.$ident;
	
	$skill = get_record_sql($sql);
	$skill->us_date_day = date('d',$skill->us_date);
	$skill->us_date_month = date('m',$skill->us_date);
	$skill->us_date_year = date('Y',$skill->us_date);
	
	if ($skill->us_date != 0){
		$skill->us_date = formatDate($skill->us_date);
	} else {
		$skill->us_date = 0;
	}
	
	$sql = 'SELECT f.ident, f.title, f.folder, f.originalname FROM '.$CFG->prefix.'userskill_evidence usevid '.
			'INNER JOIN '.$CFG->prefix.'files f ON usevid.file_id = f.ident '.
			'WHERE usevid.us_id = '.$ident;
	$evids = get_records_sql($sql);
	
	$skill->evidence = $evids;
	
	return $skill;
}

function removeSkill($skillId){
	global $USER;
	//delete the skill from user
	$where = 'ident = '.$skillId.' AND user_id = '.$USER->ident;
	delete_records_select('users_courses',$where);
	
	//delete all related users skill evidence records
	$where = 'us_id = '.$skillId;
	delete_records_select('userskill_evidence',$where);
	$delete = new StdClass;
	$delete->ident = $skillId;
	$delete->deleted = 'true';
	return $delete;
}

function getCategories($usId){
	$skillcats = get_records_select("skillcategory","","","scname ASC");
	$cats = new StdClass;
	$cats->categories = $skillcats;
	$cats->usid = $usId;
	return $cats;
}	

function getFiles($usId){
	global $page_owner;
	$filerecs = get_records_select("files","owner=".$page_owner);
	$files = new StdClass;
	$files->files = $filerecs;
	$files->usid = $usId;
	return $files;
}


function editSkill($usId,$skillId,$skillDate,$skillLoc,$skillNotes,$skillEvidence,$skillWant){
	global $CFG;
	global $page_owner;
	
	//check that page owner owns this record
	$test = get_record_select('users_skill','user_id='.$page_owner.' AND ident='.$usId);
	if(!test){
		return null;
	}
	$skill = new StdClass;
	$skill->ident = $usId;
	$skill->user_id = $page_owner;
	$skill->skill_id = $skillId;
	$skill->us_date = $skillDate;
	$skill->us_location = $skillLoc;
	$skill->us_notes = $skillNotes;
	$skill->us_want = $skillWant;
	update_record('users_skill',$skill, true);
	
	// delete existing evidence
	delete_records_select('userskill_evidence','us_id='.$usId);
	
	// add evidence
	$evidenceFiles = split(',',$skillEvidence);
	foreach ($evidenceFiles as $key => $val){
		if($val){
			$e = new StdClass;
			$e->us_id = $usId;
			$e->file_id = $val;
			insert_record('userskill_evidence',$e);
		}
	}
	
	return getUserSkill($usId);
}

?>